package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.GuestBookInfo;
import cn.com.checne.model.domain.LanguageInfo;
import cn.com.checne.model.domain.UserInfo;


@Repository
public class GuestBookInfoDao {
	protected static final Logger log = Logger.getLogger(GuestBookInfoDao.class);

	/**
	 * create
	 * @param gb
	 * @return
	 */
	public boolean create(GuestBookInfo gb) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String createSql = "insert into tbl_guestbook(lg_id_fk,gb_username,gb_email,gb_content,gb_time,gb_isshow,gb_comment_content,gb_comment_uid,gb_comment_time)" +
					" values(?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql);
			int i = 0 ;
			pstmt.setInt(++i,gb.getLanguageId());
			pstmt.setString(++i,gb.getGbUsername());
			pstmt.setString(++i,gb.getGbEmail());
			pstmt.setString(++i,gb.getGbContent());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(new java.util.Date().getTime()));
			pstmt.setInt(++i,0);
			pstmt.setString(++i,"");
			pstmt.setInt(++i,0);
			pstmt.setTimestamp(++i, new java.sql.Timestamp(new java.util.Date().getTime()));
			pstmt.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param gb
	 * @return
	 */
	public boolean update(GuestBookInfo gb) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_guestbook set lg_id_fk = ?,gb_username = ?,gb_email=?,gb_content = ?,gb_time=?,gb_isshow = ?,gb_comment_content = ?," +
					"gb_comment_uid = ?,gb_comment_time=? where gb_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,gb.getLanguageId());
			pstmt.setString(++i,gb.getGbUsername());
			pstmt.setString(++i,gb.getGbEmail());
			pstmt.setString(++i,gb.getGbContent());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(gb.getGbTime().getTime()));
			pstmt.setInt(++i,gb.getIsShow());
			pstmt.setString(++i,gb.getCommentContent());
			pstmt.setInt(++i,gb.getCommentUser());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(gb.getCommentTime().getTime()));
			pstmt.setInt(++i,gb.getGbId());
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param id
	 * @return
	 */
	public boolean delete(int id){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "delete from tbl_guestbook where gb_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,id);
			pstmt.executeUpdate();
			log.info("[SQL]" + pstmt.toString());
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	
	/**
	 * 
	 * @param gbId
	 * @return
	 */
	public GuestBookInfo getGuestBookInfo(int gbId) {
		GuestBookInfo gb = new GuestBookInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_guestbook where gb_id = ? ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, gbId);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			List<GuestBookInfo> list = getResult(rs);
			gb = (list != null && list.size() > 0 ) ? list.get(0) : gb ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return gb;
	}
	/**
	 * 
	 * @return
	 */
	public List<GuestBookInfo> getGBList(int languageId,int isShow,int isComment,int start,int num) {
		List<GuestBookInfo> list = new ArrayList<GuestBookInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_guestbook where 1 = 1" ;
			if(languageId > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(isShow > -1){
				sql += " and gb_isshow = ? " ;
			}
			if(isComment > 0){
				sql += " and gb_comment_uid >0 " ;
			}
			sql += "order by gb_time desc limit ? ,?  ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(languageId > 0){
				pstmt.setInt(++i, languageId);
			}
			if(isShow > -1){
				pstmt.setInt(++i, isShow);
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 
	 * @param languageId
	 * @param isShow
	 * @return
	 */
	public int getGBListCount(int languageId,int isShow,int isComment) {
		int total = 0 ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_guestbook where 1 = 1" ;
			if(languageId > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(isShow > -1){
				sql += " and gb_isshow = ? " ;
			}
			if(isComment > 0){
				sql += " and gb_comment_uid >0 " ;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(languageId > 0){
				pstmt.setInt(++i, languageId);
			}
			if(isShow > -1){
				pstmt.setInt(++i, isShow);
			}
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	/**
	 * get rseult from resultSet
	 * @param rs
	 * @return
	 */
	public List<GuestBookInfo> getResult(ResultSet rs) {
		List<GuestBookInfo> list = new ArrayList<GuestBookInfo>();
		try {
			GuestBookInfo gb = null ;
			while(rs.next()) {
				gb = new GuestBookInfo();
				gb.setGbId(rs.getInt("gb_id"));
				gb.setGbUsername(rs.getString("gb_username"));
				gb.setGbEmail(rs.getString("gb_email"));
				gb.setGbContent(rs.getString("gb_content"));
				gb.setGbTime(rs.getTimestamp("gb_time"));
				gb.setIsShow(rs.getInt("gb_isshow"));
				gb.setCommentTime(rs.getTimestamp("gb_comment_time"));
				gb.setCommentContent(rs.getString("gb_comment_content"));
				
				gb.setCommentUser(rs.getInt("gb_comment_uid"));
				UserInfo userInfo = new UserInfoDao().getUser(gb.getCommentUser());
				gb.setUserInfo(userInfo);
				
				gb.setLanguageId(rs.getInt("lg_id_fk"));
				LanguageInfo lg = new LanguageInfoDao().getLanguageInfoById(gb.getLanguageId());
				gb.setLanguageInfo(lg);
				
				list.add(gb);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public boolean setShowIndex(int flag,int...gbIds){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_guestbook set gb_isshow = ? where gb_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			for(int gbId : gbIds){
				int i = 0 ;
				pstmt.setInt(++i,flag);
				pstmt.setInt(++i,gbId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
}